/*
' Copyright (c) 2011 DotNetNuke Corporation
'  All rights reserved.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
' TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
' THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
' DEALINGS IN THE SOFTWARE.
'
*/

using System;
using System.Collections.Generic;
using System.Data;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Modules._2DoTasks.Components;
using DotNetNuke.Modules._2DoTasks.Components.Entities;
using Microsoft.ApplicationBlocks.Data;

namespace DotNetNuke.Modules._2DoTasks.Data
{
    /// -----------------------------------------------------------------------------
    /// <summary>
    /// SQL Server implementation of the abstract DataProvider class
    /// </summary>
    /// -----------------------------------------------------------------------------
    public class SqlDataProvider : DataProvider
    {
        #region Private Members

        private const string ProviderType = "data";
        private const string ModuleQualifier = "TDT_";

        private readonly ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private readonly string _connectionString;
        private readonly string _providerPath;
        private readonly string _objectQualifier;
        private readonly string _databaseOwner;

        #endregion Private Members

        #region Constructors

        public SqlDataProvider()
        {
            // Read the configuration specific information for this provider
            Provider objProvider = (Provider)(_providerConfiguration.Providers[_providerConfiguration.DefaultProvider]);

            // Read the attributes for this provider

            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (string.IsNullOrEmpty(_connectionString))
            {
                // Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            _objectQualifier = objProvider.Attributes["objectQualifier"];
            if (!string.IsNullOrEmpty(_objectQualifier) && _objectQualifier.EndsWith("_", StringComparison.Ordinal) == false)
            {
                _objectQualifier += "_";
            }

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if (!string.IsNullOrEmpty(_databaseOwner) && _databaseOwner.EndsWith(".", StringComparison.Ordinal) == false)
            {
                _databaseOwner += ".";
            }
        }

        #endregion Constructors

        #region Properties

        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }

        public string ProviderPath
        {
            get
            {
                return _providerPath;
            }
        }

        public string ObjectQualifier
        {
            get
            {
                return _objectQualifier;
            }
        }

        public string DatabaseOwner
        {
            get
            {
                return _databaseOwner;
            }
        }

        private string NamePrefix
        {
            get { return DatabaseOwner + ObjectQualifier + ModuleQualifier; }
        }

        #endregion Properties

        #region Private Methods

        private static object GetNull(object Field)
        {
            return DotNetNuke.Common.Utilities.Null.GetNull(Field, DBNull.Value);
        }

        #endregion Private Methods

        #region Public Methods

        #region "Task"

        /// <summary>
        /// Creates a new task
        /// </summary>
        /// <param name="t">task to create</param>
        /// <returns>returns the id of the newly created task</returns>
        public override int Add(_Task t)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, NamePrefix + "Task_Add",
                t.PortalId,
                t.ProjectId,
                t.Name,
                t.Description,
                t.ModuleID,
                t.AssignedUserId,
                t.TargetCompletiondate,
                t.CompletionDate,
                t.CreationUserId,
                t.RequestedByUserId,
                t.TaskType,
                t.Priority,
                t.TaskStatus));
        }

        /// <summary>
        /// Updates an existing task
        /// </summary>
        /// <param name="t">the task to update</param>
        public override void Update(_Task t)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Update",
                t.TaskId,
                t.Name,
                t.Description,
                t.AssignedUserId,
                t.TargetCompletiondate,
                t.CompletionDate,
                t.LastChangeUserId,
                t.TaskType,
                t.Priority,
                t.TaskStatus,
                t.ProjectId);
        }

        /// <summary>
        /// Sets the received task has complete
        /// </summary>
        /// <param name="t">the task to complete</param>
        public override void SetStatus(_Task t)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_SetStatus",
                t.TaskId,
                t.LastChangeUserId,
                t.CompletionDate,
                t.TaskStatus);
        }

        /// <summary>
        /// Deletes an existing task
        /// </summary>
        /// <param name="currentUserId">current logged user</param>
        /// <param name="t">the task to delete</param>
        public override void Delete(_Task t, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Delete", t.TaskId, currentUserId);
        }

        /// <summary>
        /// Gets an existing task
        /// </summary>
        /// <param name="id">the id of the task to collect</param>
        /// <param name="currentUserId">current logged user</param>
        /// <param name="portalId"></param>
        /// <returns>returns a task</returns>
        public override System.Data.IDataReader GetTask(int id, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Get", id, portalId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing tasks
        /// </summary>
        /// <param name="portalId"></param>
        /// <param name="currentUserId">current logged user</param>
        /// <returns>returns tasks</returns>
        public override IDataReader GetAllTasks(int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_GetAll", portalId, currentUserId);
        }

        /// <summary>
        /// Collects tasks, based on specific criteria
        /// </summary>
        /// <param name="portalId">the tasks portal</param>
        /// <param name="currentUserId">current logged user</param>
        /// <param name="assignedOptions">the assigned to tasks</param>
        /// <param name="filterAditionalOptions">specific set of filter options</param>
        /// <param name="projectId">the project the tasks belong to</param>
        /// <returns>returns tasks</returns>
        public override IDataReader FindTasks(
            int portalId, int currentUserId,
            SharedEnum.FilterAssigned assignedOptions,
            Nullable<SharedEnum.Task_FilterAditionalOptions> filterAditionalOptions,
            Nullable<int> projectId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Find", portalId, currentUserId, (int)assignedOptions,
                filterAditionalOptions.HasValue ? (int?)filterAditionalOptions.Value : null, projectId.HasValue ? (int?)projectId.Value : null);
        }

        #region "Comments"

        /// <summary>
        /// Creates a new task comment
        /// </summary>
        /// <param name="c">task comment to create</param>
        /// <param name="portalId">the task portal id</param>
        /// <returns>returns the id of the newly created task comment</returns>
        public override int Add(_TaskComment c, int portalId)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, NamePrefix + "Task_Comment_Add",
                c.TaskId,
                c.Comment,
                c.CreationUserId,
                portalId));
        }

        /// <summary>
        /// Updates an existing task comment
        /// </summary>
        /// <param name="c">the task comment to update</param>
        /// <param name="portalId">the task portal id</param>
        public override void Update(_TaskComment c, int portalId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Comment_Update",
                c.Id,
                c.TaskId,
                c.Comment,
                c.LastChangeUserId,
                portalId);
        }

        /// <summary>
        /// Deletes an existing task comment
        /// </summary>
        /// <param name="c">the task comment to delete</param>
        /// <param name="portalId">the task portal id</param>
        /// <param name="currentUserId">the current logged user</param>
        public override void Delete(_TaskComment c, int portalId, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Comment_Delete", c.Id, c.TaskId, currentUserId, portalId);
        }

        /// <summary>
        /// Gets an existing task comment
        /// </summary>
        /// <param name="id">the id of the task comment to collect</param>
        /// <param name="taskId">the id of the task it belongs to</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns a task comment</returns>
        public override IDataReader GetTaskComment(int id, int taskId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Comment_Get", id, taskId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing tasks comments
        /// </summary>
        /// <param name="taskId">the id of the task to collect the comments from</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns tasks comments</returns>
        public override IDataReader GetAllTaskComments(int taskId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Comment_GetAll", taskId, currentUserId);
        }

        #endregion "Comments"

        #region "Time"

        /// <summary>
        /// Creates a new task time entry
        /// </summary>
        /// <param name="portalId">the current portal id</param>
        /// <param name="t">task time entry to create</param>
        /// <returns>returns the id of the newly created task time entry</returns>
        public override int Add(_TaskTime t, int portalId)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, NamePrefix + "Task_Time_Add",
                t.TaskId,
                t.StartDate,
                t.EndDate,
                t.CreationUserId,
                t.Billable,
                portalId));
        }

        /// <summary>
        /// Updates an existing task time entry
        /// </summary>
        /// <param name="portalId">the current portal id</param>
        /// <param name="t">the task time entry to update</param>
        public override void Update(_TaskTime t, int portalId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Time_Update",
                t.TaskId,
                t.Id,
                t.StartDate,
                t.EndDate,
                t.LastChangeUserId,
                t.Billable,
                portalId);
        }

        /// <summary>
        /// Deletes an existing task time entry
        /// </summary>
        /// <param name="portalId">the current portalid</param>
        /// <param name="t">the task time entry to delete</param>
        /// <param name="currentUserId">the current logged user</param>
        public override void Delete(_TaskTime t, int portalId, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_Time_Delete",
                t.Id, t.TaskId,
                currentUserId,
                portalId);
        }

        /// <summary>
        /// Gets an existing task time entry
        /// </summary>
        /// <param name="id">the id of the task time entry to collect</param>
        /// <param name="taskId">the id of the task it belongs to</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns a task time entry</returns>
        public override IDataReader GetTaskTime(int id, int taskId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Time_Get", id, taskId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing tasks comments
        /// </summary>
        /// <param name="taskId">the id of the task to collect the comments from</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns tasks comments</returns>
        public override IDataReader GetAllTaskTimeEntries(int taskId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_Time_GetAll", taskId, currentUserId);
        }

        #endregion "Time"

        #region "SubTask"

        /// <summary>
        /// Creates a new sub task
        /// </summary>
        /// <param name="portalId">the current portal id</param>
        /// <param name="t">sub task to create</param>
        /// <returns>returns the id of the newly created sub task</returns>
        public override int Add(_TaskSubTask t, int portalId)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, NamePrefix + "Task_SubTask_Add",
                t.ParentTaskId,
                t.Name,
                t.AssignedUserId,
                t.CreationUserId));
        }

        /// <summary>
        /// Updates an existing sub task
        /// </summary>
        /// <param name="portalId">the current portal id</param>
        /// <param name="t">the sub task to update</param>
        public override void Update(_TaskSubTask t, int portalId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_SubTask_Update",
                t.Id,
                t.ParentTaskId,
                t.Name,
                t.AssignedUserId,
                t.CompletionDate,
                t.LastChangeUserId);
        }

        /// <summary>
        /// Updates an existing sub task, to a new order
        /// </summary>
        /// <param name="portalId">the current portal id</param>
        /// <param name="t">the sub task to update</param>
        /// <param name="newOrder">the new order value</param>
        public override void UpdateOrder(_TaskSubTask t, int portalId, int newOrder)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_SubTask_UpdateOrder",
                t.Id,
                t.ParentTaskId,
                t.Order,
                t.LastChangeUserId);
        }

        /// <summary>
        /// Deletes an existing sub task
        /// </summary>
        /// <param name="portalId">the current portalid</param>
        /// <param name="t">the sub task to delete</param>
        /// <param name="currentUserId">the current logged user</param>
        public override void Delete(_TaskSubTask t, int portalId, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Task_SubTask_Delete",
                t.Id, t.ParentTaskId,
                currentUserId);
        }

        /// <summary>
        /// Gets an existing sub task
        /// </summary>
        /// <param name="id">the id of the sub task to collect</param>
        /// <param name="taskId">the id of the task it belongs to</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <param name="portalId">the current portalid</param>
        /// <returns>returns a sub task</returns>
        public override IDataReader GetTaskSubTask(int id, int taskId, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_SubTask_Get", id, taskId, portalId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing task sub tasks
        /// </summary>
        /// <param name="taskId">the id of the task to collect the sub tasks from</param>
        /// <param name="portalId">the current portal</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns tasks sub tasks</returns>
        public override IDataReader GetAllTaskSubTaskEntries(int taskId, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Task_SubTask_GetAll", portalId, taskId, currentUserId);
        }

        #endregion "SubTask"

        #endregion "Task"

        #region "Project"

        /// <summary>
        /// Creates a new Project
        /// </summary>
        /// <param name="p">Project to create</param>
        /// <returns>returns the id of the newly created Project</returns>
        public override int Add(_Project p)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, NamePrefix + "Project_Add",
                p.PortalId,
                p.Name,
                p.Description,
                p.ModuleID,
                p.EndDate,
                p.CreatedByUserID));
        }

        /// <summary>
        /// Updates an existing Project
        /// </summary>
        /// <param name="p">the Project to update</param>
        public override void Update(_Project p)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Project_Update",
                p.ProjectId,
                p.Name,
                p.Description,
                p.EndDate,
                p.LastModifiedUserID,
                p.Archived);
        }

        /// <summary>
        /// Deletes an existing Project
        /// </summary>
        /// <param name="currentUserId">the current logged user id</param>
        /// <param name="p">the Project to delete</param>
        public override void Delete(_Project p, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "Project_Delete", p.ProjectId, currentUserId);
        }

        /// <summary>
        /// Gets an existing Project
        /// </summary>
        /// <param name="id">the id of the Project to collect</param>
        /// <param name="currentUserId">the current logged user id</param>
        /// <param name="portalId">the current portal</param>
        /// <returns>returns a Project</returns>
        public override System.Data.IDataReader GetProject(int id, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Project_Get", id, portalId, currentUserId);
        }

        /// <summary>
        /// Gets an existing Project
        /// </summary>
        /// <param name="name">the name of the Project to collect</param>
        /// <param name="portalId">current portal</param>
        /// <param name="currentUserId">the current user id</param>
        /// <returns>returns a Project</returns>
        public override System.Data.IDataReader GetProject(string name, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Project_GetByName", name, portalId, currentUserId);
        }

        /// <summary>
        /// Gets an existing Project, where the name starts with the specified parameter name
        /// </summary>
        /// <param name="name">the name of the Project to collect</param>
        /// <param name="portalId">current portal</param>
        /// <param name="currentUserId">the current user id</param>
        /// <returns>returns a Project</returns>
        public override System.Data.IDataReader GetProjectStartsWith(string name, int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Project_GetByNameStartsWith", name, portalId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing Projects
        /// </summary>
        /// <param name="portalId">the current portal</param>
        /// <param name="currentUserId">the current user id</param>
        /// <returns>returns Projects</returns>
        public override System.Data.IDataReader GetAllProjects(int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Project_GetAll", portalId, currentUserId);
        }

        /// <summary>
        /// Collects all the existing archived Projects
        /// </summary>
        /// <param name="portalId">the current portal</param>
        /// <param name="currentUserId">the current user id</param>
        /// <returns>returns Projects</returns>
        public override IDataReader GetArchived(int portalId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "Project_GetArchived", portalId, currentUserId);
        }

        #region "Project Members"

        /// <summary>
        /// Adds a new project
        /// </summary>
        /// <param name="currentUserId">the current user id</param>
        /// <param name="p">ProjectMember to create</param>
        public override void Add(_ProjectMember p, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "ProjectMember_Add",
                p.UserId,
                p.ProjectId,
                p.IsManager,
                currentUserId);
        }

        /// <summary>
        /// Adds multiple project members
        /// </summary>
        /// <param name="currentUserId">the current user id</param>
        /// <param name="p">list of project members to create</param>
        public override void AddMultiple(List<_ProjectMember> p, int currentUserId)
        {
            p.ForEach(pm =>
            {
                Add(pm, currentUserId);
            });
        }

        /// <summary>
        /// Updates an existing Project Member
        /// </summary>
        /// <param name="currentUserId">the current user id</param>
        /// <param name="p">the Project Member to update</param>
        public override void Update(_ProjectMember p, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "ProjectMember_Update",
                p.UserId,
                p.ProjectId,
                p.IsManager,
                currentUserId);
        }

        /// <summary>
        /// Deletes a project member
        /// </summary>
        /// <param name="currentUserId">the current user id</param>
        /// <param name="p">the project member to delete</param>
        public override void Delete(_ProjectMember p, int currentUserId)
        {
            SqlHelper.ExecuteNonQuery(ConnectionString, NamePrefix + "ProjectMember_Delete", p.UserId, p.ProjectId, currentUserId);
        }

        /// <summary>
        /// Deletes multiple project members
        /// </summary>
        /// <param name="p">list of project members to delete</param>
        public override void DeleteMultiple(List<_ProjectMember> p, int currentUserId)
        {
            p.ForEach(pm =>
            {
                Delete(pm, currentUserId);
            });
        }

        /// <summary>
        /// Gets an existing Project member
        /// </summary>
        /// <param name="userId">the user id</param>
        /// <param name="projectId">the project it belongs to</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns a Project member</returns>
        public override IDataReader GetProjectMember(int userId, int projectId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "ProjectMember_Get", userId, projectId, currentUserId);
        }

        /// <summary>
        /// Gets all project members
        /// </summary>
        /// <param name="projectId">the project id to collect from</param>
        /// <param name="currentUserId">the current logged user</param>
        /// <returns>returns Project members</returns>
        public override IDataReader GetAllProjectMembers(int projectId, int currentUserId)
        {
            return SqlHelper.ExecuteReader(ConnectionString, NamePrefix + "ProjectMember_GetAll", projectId, currentUserId);
        }

        /// <summary>
        /// Evaluates if the user is a project manager on the passed project
        /// </summary>
        /// <param name="currentUserId">the user to evaluate</param>
        /// <param name="projectId">the project id</param>
        /// <returns>returns true if it's a manager on the project, false otherwise</returns>
        public override Boolean IsProjectManager(int currentUserId, int projectId)
        {
            return Convert.ToBoolean(
                SqlHelper.ExecuteScalar(ConnectionString,
                CommandType.Text, string.Format("SELECT {0}Project_IsUserManager({1}, {2})", NamePrefix, currentUserId, projectId)));
        }

        #endregion "Project Members"

        #endregion "Project"

        #endregion Public Methods
    }
}